import numpy as np
import pandas as pd
from scipy.optimize import differential_evolution, minimize, linprog, shgo
from scipy.optimize import LinearConstraint, Bounds, NonlinearConstraint
from typing import Dict, List, Tuple, Any, Optional
import warnings
import pyodbc
import os
from datetime import datetime
import multiprocessing

warnings.filterwarnings('ignore')


class HybridProductOptimizer:
    def __init__(self, config: Dict[str, Any]):
        """
        混合优化器：使用DE优化单价和数量，使用线性规划优化体积和重量

        Parameters:
        - config: 配置字典，包含所有优化参数和约束
        """
        self.config = config
        self._validate_config()
        self._setup_optimization()
        config_results_dir = config.get('results_dir')
        if config_results_dir is None:
            # 如果未指定目录，自动生成带时间戳的目录
            self.results_dir = f"优化结果_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
        else:
            # 使用配置中指定的目录
            self.results_dir = config_results_dir
        os.makedirs(self.results_dir, exist_ok=True)

    def _validate_config(self):
        """验证配置完整性"""
        required_keys = ['products', 'global_constraints', 'optimization_params']
        for key in required_keys:
            if key not in self.config:
                raise ValueError(f"缺少必要配置项: {key}")

        for product in self.config['products']:
            required_product_keys = ['name', 'price_range', 'volume_range', 'weight_range']
            for key in required_product_keys:
                if key not in product:
                    raise ValueError(f"产品配置缺少 {key}")

    def _setup_optimization(self):
        """设置优化参数"""
        self.products = self.config['products']
        self.n_products = len(self.products)
        self.product_names = [p['name'] for p in self.products]

        # 提取关税信息
        self.base_tariff = np.array([p.get('base_tariff', 0) for p in self.products])
        self.add_tariff = np.array([p.get('add_tariff', 0) for p in self.products])
        self.tariff_coeff = (self.base_tariff + self.add_tariff + self.config.get('tariff_constant', 0.125))/100

        # 全局约束
        self.global_constraints = self.config['global_constraints']
        self.optimization_params = self.config['optimization_params']

    def get_price_quantity_bounds(self) -> List[Tuple[float, float]]:
        """获取单价和数量的边界条件"""
        bounds = []
        for product in self.products:
            bounds.append(product['price_range'])  # 单价
            bounds.append(product.get('quantity_range', (1, 10000)))  # 数量
        return bounds
    def print_price_quantity_bounds(self):
        """获取单价和数量的边界条件
                        'name': '牙膏',
                'price_range': (1, 10),
                'volume_range': (1, 8),
                'weight_range': (1, 5),
                'base_tariff': 2,
                'add_tariff': 1,
                'quantity_range': (10, 300)
        """
        bounds = []
        print("-----name----------------|-----price_range------|-----quantity_range------|-----volume_range------|-----weight_range------|")
        for product in self.products:
            print(
               f"{product['name']:^20}-|({product['price_range'][0]:^10.2f},{product['price_range'][1]:^10.2f})-|({product['quantity_range'][0]:^10.2f},{product['quantity_range'][1]:^10.2f})-|({product['volume_range'][0]:^10.2f},{product['volume_range'][1]:^10.2f})-|{product['weight_range'][0]:^10.2f},{product['weight_range'][1]:^10.2f}-|")


    def optimize_volume_weight(self, prices: np.ndarray, quantities: np.ndarray) -> Tuple[
        np.ndarray, np.ndarray, float]:
        """
        使用线性规划优化体积和重量

        Returns:
        - volumes: 优化后的体积
        - weights: 优化后的重量
        - objective_value: 目标函数值
        """
        # 目标函数：最小化体积和重量的总成本（这里可以自定义目标）
        # 默认使用零目标，因为主要约束在体积和重量的总和
        c = np.zeros(2 * self.n_products)  # 前n个是体积，后n个是重量

        # 约束矩阵
        A_eq = np.zeros((2, 2 * self.n_products))

        # 体积总和约束
        A_eq[0, :self.n_products] = 1
        # 重量总和约束
        A_eq[1, self.n_products:] = 1

        b_eq = np.array([
            self.global_constraints.get('total_volume', 150),
            self.global_constraints.get('total_weight', 180)
        ])

        # 边界条件
        bounds = []
        for product in self.products:
            bounds.append(product['volume_range'])  # 体积边界
        for product in self.products:
            bounds.append(product['weight_range'])  # 重量边界

        # 可选：添加与销售额相关的目标
        # 这里可以设置为最小化与初始值的偏差，或者根据业务需求设置
        sales = prices * quantities
        total_sales = np.sum(sales)

        # 如果销售额较低，可以尝试让体积和重量更均匀分布
        if total_sales < self.global_constraints.get('min_sales', 4400):
            # 倾向于让高单价商品有更大的体积和重量
            for i in range(self.n_products):
                c[i] = -prices[i] * 0.01  # 让高单价商品有更大体积
                c[self.n_products + i] = -prices[i] * 0.01  # 让高单价商品有更大重量

        try:
            result = linprog(
                c,
                A_eq=A_eq,
                b_eq=b_eq,
                bounds=bounds,
                method='highs'
            )

            if result.success:
                volumes = result.x[:self.n_products]
                weights = result.x[self.n_products:]
                return volumes, weights, result.fun
            else:
                # 如果线性规划失败，使用启发式方法
                return self._heuristic_volume_weight(prices, quantities)

        except Exception as e:
            print(f"线性规划失败: {e}, 使用启发式方法")
            return self._heuristic_volume_weight(prices, quantities)

    def _heuristic_volume_weight(self, prices: np.ndarray, quantities: np.ndarray) -> Tuple[
        np.ndarray, np.ndarray, float]:
        """启发式方法分配体积和重量"""
        target_volume = self.global_constraints.get('total_volume', 150)
        target_weight = self.global_constraints.get('total_weight', 180)

        # 按销售额比例分配
        sales = prices * quantities
        total_sales = np.sum(sales)
        sales_ratio = sales / total_sales if total_sales > 0 else np.ones(self.n_products) / self.n_products

        volumes = np.zeros(self.n_products)
        weights = np.zeros(self.n_products)

        for i, product in enumerate(self.products):
            # 在允许范围内按比例分配
            vol_min, vol_max = product['volume_range']
            weight_min, weight_max = product['weight_range']

            volumes[i] = vol_min + sales_ratio[i] * (vol_max - vol_min)
            weights[i] = weight_min + sales_ratio[i] * (weight_max - weight_min)

        # 调整到目标总和
        volumes = volumes / np.sum(volumes) * target_volume
        weights = weights / np.sum(weights) * target_weight

        # 确保在边界内
        for i, product in enumerate(self.products):
            vol_min, vol_max = product['volume_range']
            weight_min, weight_max = product['weight_range']
            volumes[i] = np.clip(volumes[i], vol_min, vol_max)
            weights[i] = np.clip(weights[i], weight_min, weight_max)

        # 最终调整
        volumes = volumes / np.sum(volumes) * target_volume
        weights = weights / np.sum(weights) * target_weight

        return volumes, weights, 0.0

    def decode_price_quantity(self, x: np.ndarray) -> Tuple[np.ndarray, np.ndarray]:
        """解码单价和数量"""
        prices = x[0::2]
        quantities = x[1::2]
        return prices, quantities

    def encode_solution(self, prices: np.ndarray, quantities: np.ndarray,
                        volumes: np.ndarray, weights: np.ndarray) -> np.ndarray:
        """编码完整解向量"""
        x = np.zeros(4 * self.n_products)
        x[0::4] = prices
        x[1::4] = quantities
        x[2::4] = volumes
        x[3::4] = weights
        return x

    def evaluate_constraints(self, prices: np.ndarray, quantities: np.ndarray,
                             volumes: np.ndarray, weights: np.ndarray) -> Dict[str, float]:
        """评估所有约束"""
        sales = prices * quantities

        return {
            'total_quantity': np.sum(quantities),
            'total_volume': np.sum(volumes),
            'total_weight': np.sum(weights),
            'total_sales': np.sum(sales),
            'total_tariff': np.sum(self.tariff_coeff * sales)
        }



    def objective_function(self, x: np.ndarray) -> float:
        """目标函数 - 只优化单价和数量"""
        prices, quantities = self.decode_price_quantity(x)

        # 使用线性规划优化体积和重量
        volumes, weights, _ = self.optimize_volume_weight(prices, quantities)

        # 评估约束
        constraints = self.evaluate_constraints(prices, quantities, volumes, weights)

        total_sales = constraints['total_sales']
        total_quantity = constraints['total_quantity']
        total_tariff = constraints['total_tariff']

        # 基础目标：总销售额接近目标值
        target_sales = self.global_constraints.get('target_sales', 5000)
        # target_penalty = abs(total_sales - target_sales) ** 2
        target_penalty = abs(total_sales - target_sales)*2

        # 约束惩罚项
        penalty = 0.0
        penalty_weight = self.optimization_params.get('penalty_weight', 10000)

        # 数量总和约束
        target_quantity_val = self.global_constraints.get('total_quantity', 1000)
        penalty += penalty_weight * abs(total_quantity - target_quantity_val)

        # 体积和重量约束已经在线性规划中处理，这里主要检查是否满足

        # 销售额范围约束
        min_sales = self.global_constraints.get('min_sales', 4400)
        max_sales = self.global_constraints.get('max_sales', 5000)
        if total_sales <= min_sales:
            penalty += penalty_weight * (min_sales - total_sales)**2
        elif total_sales > max_sales:
            penalty += penalty_weight * (total_sales - max_sales)**2

        # 关税约束
        max_tariff = self.global_constraints.get('max_tariff', 14800)
        if total_tariff >= max_tariff:
            penalty += penalty_weight * (total_tariff - max_tariff)**2
        penalty +=  abs(total_tariff - 1200)*penalty_weight*2

        # 数据尽量均衡，标准差
        std_population = np.std(quantities)

        return target_penalty + penalty +std_population

    def is_feasible(self, prices: np.ndarray, quantities: np.ndarray,
                    volumes: np.ndarray, weights: np.ndarray, tolerance: float = 1e-3) -> bool:
        """检查解是否可行"""
        constraints = self.evaluate_constraints(prices, quantities, volumes, weights)

        target_quantity = self.global_constraints.get('total_quantity', 1000)
        target_volume = self.global_constraints.get('total_volume', 150)
        target_weight = self.global_constraints.get('total_weight', 180)
        min_sales = self.global_constraints.get('min_sales', 4400)
        max_sales = self.global_constraints.get('max_sales', 5000)
        max_tariff = self.global_constraints.get('max_tariff', 14800)

        return (
                abs(constraints['total_quantity'] - target_quantity) < tolerance and
                abs(constraints['total_volume'] - target_volume) < tolerance and
                abs(constraints['total_weight'] - target_weight) < tolerance and
                min_sales < constraints['total_sales'] <= max_sales + tolerance and
                constraints['total_tariff'] < max_tariff + tolerance
        )

    def sum_constraint(self, x: np.ndarray):
        """目标函数 - 只优化单价和数量"""
        prices, quantities = self.decode_price_quantity(x)
        # 评估约束
        # 使用线性规划优化体积和重量
        volumes, weights, _ = self.optimize_volume_weight(prices, quantities)

        constraints = self.evaluate_constraints(prices, quantities, volumes, weights)

        total_sales = constraints['total_sales']
        total_quantity = constraints['total_quantity']
        total_tariff = constraints['total_tariff']

        target_sales = self.global_constraints.get('target_sales', 5000)

        # 约束惩罚项
        penalty_weight = self.optimization_params.get('penalty_weight', 10000)

        # 数量总和约束
        target_quantity_val = self.global_constraints.get('total_quantity', 1000)
        max_tariff = self.global_constraints.get('max_tariff', 1000)
        tax = 0
        if total_tariff > max_tariff:
            tax = 1

        return np.array([total_sales-target_sales, total_quantity-target_quantity_val,tax])

    def optimize(self) -> Tuple[np.ndarray, np.ndarray, np.ndarray, np.ndarray]:
        """执行混合优化"""
        bounds = self.get_price_quantity_bounds()
        n_cores = multiprocessing.cpu_count()
        print(f"第一阶段：全局搜索单价和数量:{self.optimization_params}...")

        # result_stage1 = shgo(
        #     self.objective_function,
        #     bounds=bounds,
        #     n=self.optimization_params.get('n_samples', 100),
        #     iters=self.optimization_params.get('iters', 300),
        #     sampling_method='simplicial',  # 改用拉丁超立方采样
        #     options={
        #         'disp': True,
        #         'f_tol': self.optimization_params.get('tol', 1e-7),
        #         'maxfev': self.optimization_params.get('maxfev', 5000),
        #         'minhgrd': 1,
        #         'qhull_options': 'QJ',  # 关键：添加joggle选项
        #     }
        # )
        constraints = NonlinearConstraint(self.sum_constraint, 0, 0)  # lb=0, ub=0 表示等于10

        result_stage1 = differential_evolution(
            self.objective_function,
            bounds,
            strategy='best1bin',
            # strategy='best1bin',
            maxiter=self.optimization_params.get('maxiter_first', 50),
            popsize=self.optimization_params.get('popsize_first', 20),
            tol=self.optimization_params.get('tol', 1e-4),
            seed=self.optimization_params.get('seed', 42),
            disp=True,
            polish=True,
            init = 'random',
            workers=n_cores,  # 关键：使用多进程
            updating='deferred'  # 并行时必须设置
        )

        print("第二阶段：局部优化单价和数量...")
        result_stage2 = minimize(
            self.objective_function,
            result_stage1.x,
            method='trust-constr',
            constraints=constraints,

            bounds=bounds,
            options={
                'maxiter': self.optimization_params.get('maxiter_second', 200),
                # 'ftol': self.optimization_params.get('ftol', 1e-8)
            }
        )
        if not result_stage2.success:
            print(f"第二阶段：局部优化失败：{result_stage2.message}")


        # 获取最终解
        final_prices, final_quantities = self.decode_price_quantity(
            result_stage2.x if result_stage2.success else result_stage1.x
        )
        final_volumes, final_weights, _ = self.optimize_volume_weight(final_prices, final_quantities)

        return final_prices, final_quantities, final_volumes, final_weights

    def integer_adjustment(self, prices: np.ndarray, quantities: np.ndarray,
                           volumes: np.ndarray, weights: np.ndarray) -> Tuple[
        np.ndarray, np.ndarray, np.ndarray, np.ndarray]:
        """整数调整"""
        integer_quantities = np.round(quantities).astype(int)
        target_quantity = self.global_constraints.get('total_quantity', 1000)
        total = np.sum(integer_quantities)
        diff = target_quantity - total

        if diff != 0:
            adjustments = []
            current_sales = np.sum(prices * integer_quantities)

            for i in range(self.n_products):
                if diff > 0:
                    test_quantities = integer_quantities.copy()
                    test_quantities[i] += 1
                    new_sales = np.sum(prices * test_quantities)
                    impact = abs(new_sales - current_sales)
                    adjustments.append((impact, i, 1))
                else:
                    if integer_quantities[i] > 1:
                        test_quantities = integer_quantities.copy()
                        test_quantities[i] -= 1
                        new_sales = np.sum(prices * test_quantities)
                        impact = abs(new_sales - current_sales)
                        adjustments.append((impact, i, -1))

            adjustments.sort()

            for impact, idx, change in adjustments:
                if diff == 0:
                    break
                if (diff > 0 and change > 0) or (diff < 0 and change < 0):
                    integer_quantities[idx] += change
                    diff -= change

        # 重新优化体积和重量
        new_volumes, new_weights, _ = self.optimize_volume_weight(prices, integer_quantities)

        return prices, integer_quantities, new_volumes, new_weights

    def format_solution(self, prices: np.ndarray, quantities: np.ndarray,
                        volumes: np.ndarray, weights: np.ndarray) -> Tuple[pd.DataFrame, Dict]:
        """格式化输出结果"""
        constraints = self.evaluate_constraints(prices, quantities, volumes, weights)

        result_df = pd.DataFrame({
            '商品名称': self.product_names,
            '基础关税': self.base_tariff,
            '附加关税': self.add_tariff,
            '关税系数': np.round(self.tariff_coeff, 3),
            '单价': np.round(prices, 4),
            '数量': quantities.astype(int),
            '体积': np.round(volumes, 4),
            '重量': np.round(weights, 4),
            '销售额': np.round(prices * quantities, 2),
            '关税成本': np.round(self.tariff_coeff * prices * quantities, 2)
        })

        target_quantity = self.global_constraints.get('total_quantity', 1000)
        target_volume = self.global_constraints.get('total_volume', 150)
        target_weight = self.global_constraints.get('total_weight', 180)
        min_sales = self.global_constraints.get('min_sales', 4400)
        max_sales = self.global_constraints.get('max_sales', 5000)
        max_tariff = self.global_constraints.get('max_tariff', 14800)

        summary = {
            '数量总和': f"{int(constraints['total_quantity'])} (目标: {target_quantity})",
            '体积总和': f"{np.round(constraints['total_volume'], 6)} (目标: {target_volume})",
            '重量总和': f"{np.round(constraints['total_weight'], 6)} (目标: {target_weight})",
            '总销售额': f"{np.round(constraints['total_sales'], 2)} (范围: {min_sales}-{max_sales})",
            '关税总额': f"{np.round(constraints['total_tariff'], 2)} (约束: <{max_tariff})",
            '解是否可行': self.is_feasible(prices, quantities, volumes, weights)
        }

        return result_df, summary


# 配置函数（保持不变）
def create_sample_config(excel_data):
    """创建示例配置"""
    config = {
        'products': [
            {
                'name': '牙膏',
                'price_range': (1, 10),
                'volume_range': (1, 8),
                'weight_range': (1, 5),
                'base_tariff': 2,
                'add_tariff': 1,
                'quantity_range': (10, 300)
            },
            {
                'name': '牙刷',
                'price_range': (1, 10),
                'volume_range': (1, 8),
                'weight_range': (1, 5),
                'base_tariff': 2,
                'add_tariff': 1,
                'quantity_range': (10, 300)
            },
            {
                'name': '牙签',
                'price_range': (1, 10),
                'volume_range': (1, 8),
                'weight_range': (1, 5),
                'base_tariff': 2,
                'add_tariff': 1,
                'quantity_range': (10, 300)
            },
            {
                'name': '被子',
                'price_range': (50, 200),
                'volume_range': (50, 100),
                'weight_range': (10, 50),
                'base_tariff': 2,
                'add_tariff': 1,
                'quantity_range': (1, 100)
            },
            {
                'name': '桌子',
                'price_range': (50, 200),
                'volume_range': (50, 200),
                'weight_range': (10, 150),
                'base_tariff': 2,
                'add_tariff': 1,
                'quantity_range': (1, 50)
            },
            {
                'name': '杯子',
                'price_range': (1, 20),
                'volume_range': (1, 8),
                'weight_range': (1, 5),
                'base_tariff': 2,
                'add_tariff': 1,
                'quantity_range': (10, 200)
            }
        ],

        'global_constraints': {
            'total_quantity': 936,
            'total_volume': 68.1,
            'total_weight': 12369,
            'min_sales': 4500,
            'max_sales': 4900,
            'target_sales': 5000,
            'max_tariff': 1800
        },

        'optimization_params': {
            'maxiter_first': 1000,
            'maxiter_second': 2000,
            'popsize_first': 20,
            'penalty_weight': 10,
            'tol': 1e-3,
            'ftol': 1e-8,
            'seed': 42
        },

        'tariff_constant': 0.125
    }

    column_mapping = {
        '英文品名': 'name',
        '基础关税': 'base_tariff',
        '附加关税': 'add_tariff',
        '产品申报数量': 'quantity_range',
        '收货实重': 'weight_range',
        '方数': 'volume_range',
        '产品申报单价': 'price_range'
    }

    config['products'].clear()
    for index, row in excel_data.iterrows():
        row_dict = {}

        for col_name, value in row.items():
            json_key = column_mapping.get(col_name, None)
            if json_key is None:
                continue

            if pd.isna(value):
                continue
            if  json_key in ['base_tariff','add_tariff','name']:
                row_dict[json_key] = value
                continue
            if json_key == 'price_range':
                row_dict[json_key] = (min(row['min_div_2'],0.1), row['max_mul_2']*3)
                continue
            if json_key == 'quantity_range':
                row_dict[json_key] = (1, config['global_constraints']['total_quantity'])
                continue

            row_dict[json_key] = (min(1,value/2), value * 2)
        config['products'].append(row_dict)
    return config


def main(excel_data):
    # 使用混合优化器
    config = create_sample_config(excel_data)


    optimizer = HybridProductOptimizer(config)

    print("=" * 80)
    print("关税信息和优化方法:")
    print("=" * 80)
    print("商品名称\t基础关税\t附加关税\t关税系数")
    for i, product in enumerate(config['products']):
        tariff_coeff = product.get('base_tariff', 0) + product.get('add_tariff', 0) + config.get('tariff_constant',
                                                                                                 0.125)
        print(
            f"{product['name']}\t{product.get('base_tariff', 0)}\t\t{product.get('add_tariff', 0)}\t\t{tariff_coeff:.3f}")

    print("\n优化策略: 差分进化(单价和数量) + 线性规划(体积和重量)")
    print("开始优化...")

    # 执行优化
    prices, quantities, volumes, weights = optimizer.optimize()

    # 调整数量为整数
    final_prices, final_quantities, final_volumes, final_weights = optimizer.integer_adjustment(
        prices, quantities, volumes, weights
    )

    # 输出结果
    result_df, summary = optimizer.format_solution(
        final_prices, final_quantities, final_volumes, final_weights
    )

    print("\n" + "=" * 80)
    print("优化结果:")
    print("=" * 80)
    print(result_df.to_string(index=False))

    print("\n" + "=" * 80)

    optimizer.print_price_quantity_bounds()
    print("约束验证:")
    print("=" * 80)
    for key, value in summary.items():
        print(f"{key}: {value}")

    # 保存结果

    save_file = os.path.join(optimizer.results_dir, "优化结果.xlsx")
    print(f"\n结果已保存到 {save_file}")
    result_df.to_excel(save_file, index=False)


def read_excel_data(file_path):
    """
    读取Excel文件的Sheet1数据

    参数:
    file_path (str): Excel文件路径

    返回:
    pandas.DataFrame: 包含Sheet1数据的DataFrame
    """
    try:
        # 检查文件是否存在
        if not os.path.exists(file_path):
            raise FileNotFoundError(f"文件 '{file_path}' 不存在")

        # 读取Sheet1数据
        df = pd.read_excel(file_path, sheet_name='Sheet1')

        print(f"成功读取文件: {file_path}")
        print(f"数据形状: {df.shape} (行数: {df.shape[0]}, 列数: {df.shape[1]})")
        print("\n前5行数据:")
        print(df.head())

        return df

    except Exception as e:
        print(f"读取文件时出错: {e}")
        return None

# 自定义函数返回(min, max)元组
def min_max_tuple(series):
    return (series.min()/2, series.max()*2)


def get_latest_customs_data_pandas(db_path, name_list):
    """
    使用pandas从Access数据库中读取"清关资料"表的最新数据

    Args:
        db_path (str): Access数据库文件路径

    Returns:
        dict: 最新一条数据的字典，如果没有数据则返回None
    """
    try:
        # 创建数据库连接字符串
        conn_str = (
            r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
            f'DBQ={db_path};'
        )

        # 建立数据库连接
        conn = pyodbc.connect(conn_str)
        # 构建IN子句中的占位符
        placeholders = ', '.join(['?' for _ in name_list])

        # SQL查询语句
        sql_query = f"""
        SELECT * FROM 清关资料 where 英文品名 in ({placeholders})
        ORDER BY 导入时间 DESC
        """
        # 读取数据到pandas DataFrame
        df = pd.read_sql(sql_query, conn, params=name_list)

        # 关闭连接
        conn.close()
        # 正确做法3：先计算聚合结果，再合并回原数据
        df3 = df.copy()

        # 计算约束统计量
        constraint_stats = df3.groupby('英文品名').agg(
            min_div_2=('产品申报单价', lambda x: x.min()),
            max_mul_2=('产品申报单价', lambda x: x.max())
        ).reset_index()
        # 合并回原数据
        df = df3.merge(constraint_stats, on='英文品名', how='left')
        df = df.drop_duplicates(subset=['英文品名'])

        return df

    except pyodbc.Error as e:
        print(f"数据库连接错误: {e}")
        return None
    except Exception as e:
        print(f"发生错误: {e}")
        return None


def update_excel_data_advanced(df_a, df_b, update_columns=None):
    """
    高级版本：可以指定需要更新的列

    Parameters:
    file_a: 原始Excel文件路径
    file_b: 包含更新数据的Excel文件路径
    update_columns: 需要更新的列名列表，如果为None则更新所有列（除英文品名外）
    output_file: 输出文件路径
    """


    # 如果没有指定更新列，则更新除英文品名外的所有列
    if update_columns is None:
        update_columns = [col for col in df_a.columns if col != '英文品名']

    print(f"将更新以下列: {update_columns}")

    # 方法2：使用迭代更新（更直观的方法）
    updated_count = 0

    for idx_b, row_b in df_b.iterrows():
        english_name = row_b['英文品名']

        # 在df_a中查找相同英文品名的行
        mask = df_a['英文品名'] == english_name

        if mask.any():
            # 更新找到的行
            for col in update_columns:
                if col in row_b and pd.notna(row_b[col]):
                    df_a.loc[mask, col] = row_b[col]

            updated_count += 1

    print(f"成功更新了{updated_count}条记录")

    return df_a

if __name__ == "__main__":
    # 替换为你的Access数据库文件路径
    db_path = r"D:\programData\access\test.accdb"
    excel_path = r"D:\tmp\test.xlsx"

    excel_data = read_excel_data(excel_path)
    name_list = excel_data['英文品名'].tolist()

    db_data = get_latest_customs_data_pandas(db_path, name_list)

    columns_to_update = ['产品材质', '产品海关编码', '基础关税', '附加关税', '产品申报单价','max_mul_2','min_div_2']

    # 使用数据库的数据更新excel中的数据
    update_excel_data_advanced(excel_data, db_data, columns_to_update)

    main(excel_data)